﻿using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Threading;
using System.Collections;
using System.Windows.Forms;
using System.Collections.Generic;
using WMSADBUtility;
using ElectronicWMSA.Comm.ClassFiles;
using ElectronicWMSA.Comm.Forms.Message;
using SYDZ.WMS.HelpClass;
using WMSAML.SY;
using WMSABL.SY;

namespace ElectronicWMSA.List.Second
{
    public partial class ImportWindos : Form
    {
        public bool ISOK = false;
        public ImportWindos()
        {
            InitializeComponent();
        }

        List<string> sqllist1 = new List<string>();
        DataTable Data_Excel = new DataTable();
        DataTable Data_Info = new DataTable();

        string FileName;
        private void btnSelectFiles_Click(object sender, EventArgs e)
        { 
            try
            {
                string filename = "";
                INPortExcel inPexcel = new INPortExcel();
                OpenFileDialog fileDialog = new OpenFileDialog();
                if (fileDialog.ShowDialog() == DialogResult.OK)
                {
                    dgv1.DataSource = null;
                    dgv2.DataSource = null;
                    sqllist1.Clear();
                    txt_Path.Text = "";

                    string extension = Path.GetExtension(fileDialog.FileName);
                    txt_Path.Text = Path.GetFullPath(fileDialog.FileName);
                    string[] str = new string[] { ".xls", ".xlsx" };
                    if (!((IList)str).Contains(extension))
                    {
                        MessageBox.Show("仅能上传xls,xlsx格式的文件！");
                    }
                    else
                    {
                        filename = fileDialog.FileName;
                        FileName = filename;
                        if (inPexcel.IsFileInUse(filename))
                        {
                            MessageBox.Show("文件已打开或被其他程序占用，请检查并关闭占用程序！", "提示");
                        }
                    }
                    WaitingBoxFrm wf = new WaitingBoxFrm((obj, args) =>
                    {
                        Thread.Sleep(100);
                        InsertData(filename);
                    }, 1800);
                    wf.ShowDialog(this);

                    dgv1.DataSource = Data_Excel;
                    dgv2.DataSource = Data_Info;
                    int num = 0;
                    if (_Abnormal_StrID != "")
                    {
                        string[] arr = _Abnormal_StrID.Split(',');
                        for (int i = arr.Length - 1; i > -1; i--)
                        {
                            if (arr[i].ToString() != "")
                            {

                                this.dgv1.Rows[Convert.ToInt32(arr[i])].DefaultCellStyle.BackColor = Color.Red;
                                num++;
                            }
                        }
                        dgv1.FirstDisplayedScrollingRowIndex = Convert.ToInt32(arr[num - 1]);
                        MessageClass.warning("【" + num + "】条数据存在异常，请确认");
                    }
                    else
                    {
                        if (dgv1.Rows.Count > 0) dgv1.FirstDisplayedScrollingRowIndex = dgv1.Rows.Count - 1;
                    }
                    if (dgv2.Rows.Count > 0) dgv2.FirstDisplayedScrollingRowIndex = dgv2.Rows.Count - 1;
                }
            }
            catch (Exception ex)
            {
                MessageClass.error("数据异常：" + ex.ToString());
            }
        }
        private string _Abnormal_StrID = "";
        private string _ARepeat_StrID = "";
        private void InsertData(string filename)
        {
            try
            {
                sqllist1.Clear();
                _Abnormal_StrID = "";
                _ARepeat_StrID = "";
                Data_Info = new DataTable();
                Data_Excel = new DataTable();
                Data_Excel = INPortExcel.ExcelToTable(filename);
                if (Data_Excel.Rows.Count > 0)
                {
                    Data_Info = Data_Excel.Copy();
                    string Tracking_No = "", Model_No = "", Serial_No = "", City = "";
                    for (int i = 0; i < Data_Excel.Rows.Count; i++)
                    {
                        try
                        {
                            Tracking_No = Data_Excel.Rows[i]["Tracking No"].ToString().Trim();
                            Model_No = Data_Excel.Rows[i]["Model No"].ToString().Trim();
                            Serial_No = Data_Excel.Rows[i]["Serial No"].ToString().Trim();
                            City = Data_Excel.Rows[i]["City"].ToString().Trim();
                        }
                        catch (Exception)
                        {
                            MessageClass.error("数据异常:表头名字错误\n请确定Excel表头名字是否包含:Tracking No,Model No,Serial No,City");
                            Data_Info = null;
                            Tracking_No = "";
                            Model_No = ""; 
                            Serial_No = ""; 
                            City = "";
                            return;
                        }


                        //防呆设置
                        if (Tracking_No == "" || Model_No == "" || Serial_No == "" || City == "")
                        {
                            _ARepeat_StrID += "," + i.ToString(); _Abnormal_StrID += "," + i.ToString();
                        }
                    }

                    if (_ARepeat_StrID != "")
                    {
                        string[] arr = _ARepeat_StrID.Split(',');
                        for (int i = arr.Length - 1; i > -1; i--)
                        {
                            if (arr[i].ToString() != "")
                                Data_Info.Rows.RemoveAt(Convert.ToInt32(arr[i]));
                        }
                    }



                }
            }
            catch (Exception ex)
            {
                MessageClass.error("数据异常:" + ex.ToString());
                Data_Info = null;
            }
        }
        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (MessageClass.enquire("确认导入共:【" + Data_Info.Rows.Count + "】条信息？") == DialogResult.OK)
            {
                WaitinglodingFrm wf = new WaitinglodingFrm((obj, args) => { Thread.Sleep(100); Import(); }, 500);
                wf.ShowDialog(this);
                if (ISOK == true)
                {
                    UploadExcelInformation(FileName);
                    MessageClass.right("操作成功！");
                    dgv1.DataSource = null;
                    dgv2.DataSource = null;
                    Data_Excel = new DataTable();
                    Data_Info = new DataTable();
                    sqllist1.Clear();
                    txt_Path.Text = "";
                }
            }
        }

        #region 导入
        private void Import()
        {
                string Tracking_No = "", Model_No = "", Serial_No = "", City = "";
                string _User = SystemVariable.Login_Name;
                string _Time = DateTime.Now.ToString("yyyy-MM-dd");
                DataTable dt = DBHelper.GetDataSet("SELECT * FROM  [T_UPS_Excel_Manage]").Tables[0];
                int G1 = 0, G2 = 0;
                if (Data_Info.Rows.Count > 0)
                {
                    for (int i = 0; i < Data_Info.Rows.Count; i++)
                    {
                        Tracking_No = Data_Info.Rows[i]["Tracking No"].ToString().Trim();
                        Model_No = Data_Info.Rows[i]["Model No"].ToString().Trim();
                        Serial_No = Data_Info.Rows[i]["Serial No"].ToString().Trim();
                        City = Data_Info.Rows[i]["City"].ToString().Trim();
                        
                        DataRow[] _DataRow = dt.Select("Tracking_No='" + Tracking_No + "'");
                    
                    if (_DataRow.Length == 0)
                        {
                        
                        string content = City.Trim();
                        content = content.Replace("'", "''");

                        string sql = "insert into [T_UPS_Excel_Manage] (Tracking_No,Model_No,Serial_No,City,Create_Time)values" +
                                "('" + Tracking_No + "','" + Model_No + "','" + Serial_No + "','" + content + "','" + DateTime.Now + "' )";

                            DBHelper.ExecuteSql(sql);
                        G1++;
                        }
                        else
                        {
                        string content = City.Trim();
                        content = content.Replace("'", "''");

                        string sql = "update [T_UPS_Excel_Manage] set " +
                            " Tracking_No= '" + Tracking_No + "', " +
                            "    Model_No= '" + Model_No + "', " +
                            "   Serial_No= '" + Serial_No + "', " +
                            "        City= '" + content + "', " +
                            " Modify_Time= '" + DateTime.Now + "' " +
                            "where Tracking_No = '" + Tracking_No + "'";
                        DBHelper.ExecuteSql(sql);
                        G2++;
                        }
                    
                    }

            }
            MessageBox.Show("你插入了 " + G1 + " 行数据\n" + "修改了 " + G2 + " 行数据");
            ISOK = true;
            SystemVariable.AddLogInfo("文件导入", "操作员：【" + SystemVariable.Login_Name + "】导入了Excel文件"+FileName);
        }
        #endregion

        #region 上传Excel表格
        private void UploadExcelInformation(string path) {
            ExcelLog_BLL bll = new ExcelLog_BLL();
            ExcelLog_Entity entity = new ExcelLog_Entity();

            //文件传二进制
            entity = new ExcelLog_Entity();
            entity.ExcelName = path;
            byte[] bytesl = null;
            FileStream fsl = new FileStream(path, FileMode.Open);
            bytesl = new byte[fsl.Length];
            fsl.Read(bytesl, 0, (int)fsl.Length);
            entity.ExcelFile = bytesl;
            entity.CreatTime = DateTime.Now;
            int rows = bll.Add(entity);
            

        }
        #endregion

        private void dgv1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            SolidBrush b = new SolidBrush(dgv1.RowHeadersDefaultCellStyle.ForeColor);
            e.Graphics.DrawString((e.RowIndex + 1).ToString(System.Globalization.CultureInfo.CurrentUICulture), dgv1.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4);
        }

        private void dgv2_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            SolidBrush b = new SolidBrush(dgv2.RowHeadersDefaultCellStyle.ForeColor);
            e.Graphics.DrawString((e.RowIndex + 1).ToString(System.Globalization.CultureInfo.CurrentUICulture), dgv2.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4);
        }

        private void ImportWindos_Load(object sender, EventArgs e)
        {

        }

        private void Instruction_Click(object sender, EventArgs e)
        {
            Instruction_ImportWindows ins = new Instruction_ImportWindows();
            ins.ShowDialog();
        }
    }
}
